﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitor_IndexUsageGather]
#-- Purpose:		Gathers index usage metrics and stores in a table
#--					for future review
#--	Last Update:	06/04/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitor_IndexUsageGather]
AS

--- Declare Local Variables
DECLARE @sSQL nvarchar(MAX), @sSQL_Name nvarchar(MAX)
DECLARE @source TABLE
(
	[ius_sql_starttime] [datetime] NOT NULL, 
	[ius_databaseid] [int] NOT NULL, 
	[ius_tableid] [int] NOT NULL, 
	[ius_indexid] [int] NOT NULL, 
	[ius_type] [tinyint] NOT NULL, 
	[ius_is_disabled] [bit] NOT NULL DEFAULT (0), 
	[ius_user_updates] [int] NOT NULL DEFAULT (0), 
	[ius_user_uses] [int] NOT NULL DEFAULT (0), 
	[ius_system_updates] [int] NOT NULL DEFAULT (0), 
	[ius_system_uses] [int] NOT NULL DEFAULT (0), 
	[ius_is_pkey] [bit] NOT NULL DEFAULT (0), 
	[ius_data_space_id] [int] NOT NULL
)
DECLARE @sourceNames TABLE
(
	[bti_id] INT IDENTITY(1, 1) NOT NULL, 
	[bti_startdate] DATETIME NOT NULL,
	[bti_databaseid] INT NOT NULL, 
	[bti_tableid] INT NOT NULL, 
	[bti_indexid] INT NOT NULL, 
	[bti_databasename] VARCHAR(256) NULL, 
	[bti_tablename] VARCHAR(256) NULL, 
	[bti_indexname] VARCHAR(256) NULL 
)

SET NOCOUNT ON

--- Gather current index usage statistics, and sql server start time
SELECT	@sSQL = 'USE [?];
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
	SELECT		(select login_time from [sys].[sysprocesses] where spid=1) start_time,
				DB_ID() database_id,
				i.object_id,
				i.index_id,
				i.type,
				i.is_disabled,
				(CASE	WHEN iuse.user_updates > 2147483647 THEN 2147483647
						ELSE iuse.user_updates
						END) user_updates,
				(CASE	WHEN (iuse.user_seeks + iuse.user_scans + iuse.user_lookups) > 2147483647 THEN 2147483647
						ELSE (iuse.user_seeks + iuse.user_scans + iuse.user_lookups)
						END) user_uses,
				(CASE	WHEN iuse.system_updates > 2147483647 THEN 2147483647
						ELSE iuse.system_updates
						END) system_updates,
				(CASE	WHEN (iuse.system_seeks + iuse.system_scans + iuse.system_lookups) > 2147483647 THEN 2147483647
						ELSE (iuse.system_seeks + iuse.system_scans + iuse.system_lookups)
						END) system_uses,
				i.is_primary_key,
				i.data_space_id
	FROM		sys.indexes i
	JOIN		sys.dm_db_index_usage_stats iuse ON i.object_id = iuse.object_id
				AND i.index_id = iuse.index_id
	WHERE		iuse.database_id = DB_ID()
	ORDER BY	OBJECT_NAME(i.object_id),
				i.name;',
		@sSQL_Name = 'USE [?];
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
	SELECT		MIN([start_time]) [start_time],
				[database_id],
				[object_id],
				[index_id],
				[database_name],
				[table_name],
				[index_name]
	FROM		(
				SELECT		(select login_time from [sys].[sysprocesses] where spid=1) [start_time],
							DB_ID() [database_id],
							i.[object_id],
							i.[index_id],
							DB_NAME() [database_name],
							OBJECT_NAME(i.[object_id]) [table_name],
							i.[name] [index_name]
				FROM		sys.indexes i
				JOIN		sys.dm_db_index_usage_stats iuse ON i.[object_id] = iuse.[object_id]
							AND i.[index_id] = iuse.[index_id]
				WHERE		iuse.[database_id] = DB_ID()
				) objs
	GROUP BY	[database_id],
				[object_id],
				[index_id],
				[database_name],
				[table_name],
				[index_name];'
INSERT INTO @source
	EXEC	sp_MSforeachdb @command1 = @sSQL;
INSERT INTO @sourceNames
	EXEC	sp_MSforeachdb @command1 = @sSQL_Name;

SET NOCOUNT OFF;


---------------------------------------------------------------------
--- BEGIN:  Index stats
---

--- Remove records that no longer exist; this shouldn't happen
DELETE		iuse
FROM		[dbo].[tblMonitor_IndexUsage] iuse
LEFT JOIN	@source iuse_new ON iuse.ius_sql_starttime = iuse_new.ius_sql_starttime
			AND iuse.ius_databaseid = iuse_new.ius_databaseid
			AND iuse.ius_tableid = iuse_new.ius_tableid
			AND iuse.ius_indexid = iuse_new.ius_indexid
WHERE		iuse_new.ius_databaseid IS NULL

--- Update records that have changed
UPDATE		iuse
SET			ius_type = iuse_new.ius_type,
			ius_is_disabled = iuse_new.ius_is_disabled,
			ius_user_updates = iuse_new.ius_user_updates,
			ius_user_uses = iuse_new.ius_user_uses,
			ius_system_updates = iuse_new.ius_system_updates,
			ius_system_uses = iuse_new.ius_system_uses,
			ius_is_pkey = iuse_new.ius_is_pkey,
			ius_data_space_id = iuse_new.ius_data_space_id
FROM		[dbo].[tblMonitor_IndexUsage] iuse
JOIN		@source iuse_new ON iuse.ius_sql_starttime = iuse_new.ius_sql_starttime
			AND iuse.ius_databaseid = iuse_new.ius_databaseid
			AND iuse.ius_tableid = iuse_new.ius_tableid
			AND iuse.ius_indexid = iuse_new.ius_indexid
WHERE		NOT (
			iuse.ius_type = iuse_new.ius_type
			AND iuse.ius_is_disabled = iuse_new.ius_is_disabled
			AND iuse.ius_user_updates = iuse_new.ius_user_updates
			AND iuse.ius_user_uses = iuse_new.ius_user_uses
			AND iuse.ius_system_updates = iuse_new.ius_system_updates
			AND iuse.ius_system_uses = iuse_new.ius_system_uses
			AND iuse.ius_is_pkey = iuse_new.ius_is_pkey
			AND iuse.ius_data_space_id = iuse_new.ius_data_space_id
			)

--- Add new records
INSERT INTO [dbo].[tblMonitor_IndexUsage]
(
	ius_sql_starttime,
	ius_databaseid,
	ius_tableid,
	ius_indexid,
	ius_type,
	ius_is_disabled,
	ius_user_updates,
	ius_user_uses,
	ius_system_updates,
	ius_system_uses,
	ius_is_pkey,
	ius_data_space_id
)
SELECT		iuse_new.ius_sql_starttime,
			iuse_new.ius_databaseid,
			iuse_new.ius_tableid,
			iuse_new.ius_indexid,
			iuse_new.ius_type,
			iuse_new.ius_is_disabled,
			iuse_new.ius_user_updates,
			iuse_new.ius_user_uses,
			iuse_new.ius_system_updates,
			iuse_new.ius_system_uses,
			iuse_new.ius_is_pkey,
			iuse_new.ius_data_space_id
FROM		[dbo].[tblMonitor_IndexUsage] iuse
RIGHT JOIN	@source iuse_new ON iuse.ius_sql_starttime = iuse_new.ius_sql_starttime
			AND iuse.ius_databaseid = iuse_new.ius_databaseid
			AND iuse.ius_tableid = iuse_new.ius_tableid
			AND iuse.ius_indexid = iuse_new.ius_indexid
WHERE		iuse.ius_databaseid IS NULL
---
--- END:  Index stats
---------------------------------------------------------------------

---------------------------------------------------------------------
--- BEGIN:  Database, Table, Index names
---

--- If this is the first insert to the table, use a very low start date
IF NOT EXISTS (SELECT TOP 1 [bti_startdate] FROM [dbo].[tblMonitor_DbTblInd_Names])
	UPDATE	@sourceNames
	SET		[bti_startdate] = CAST('01/01/1900' as datetime)

--- Disable Database/Table/Index values that no longer exist
UPDATE		iuse
SET			[bti_enddate] = ISNULL(DATEADD(ss, -1, endValue.[bti_startdate]), GETDATE())
FROM		[dbo].[tblMonitor_DbTblInd_Names] iuse
LEFT JOIN	@sourceNames iuse_new ON iuse.[bti_databaseid] = iuse_new.[bti_databaseid]
			AND iuse.[bti_tableid] = iuse_new.[bti_tableid]
			AND ISNULL(iuse.[bti_indexid], 0) = ISNULL(iuse_new.[bti_indexid], 0)
			AND iuse.[bti_databasename] = iuse_new.[bti_databasename]
			AND iuse.[bti_tablename] = iuse_new.[bti_tablename]
			AND ISNULL(iuse.[bti_indexname], SPACE(0)) = ISNULL(iuse_new.[bti_indexname], SPACE(0))
LEFT JOIN	@sourceNames endValue ON iuse.[bti_databaseid] = endValue.[bti_databaseid]
			AND iuse.[bti_tableid] = endValue.[bti_tableid]
			AND ISNULL(iuse.[bti_indexid], 0) = ISNULL(endValue.[bti_indexid], 0)
			AND NOT (
				iuse.[bti_databasename] = endValue.[bti_databasename]
				AND iuse.[bti_tablename] = endValue.[bti_tablename]
				AND ISNULL(iuse.[bti_indexname], SPACE(0)) = ISNULL(endValue.[bti_indexname], SPACE(0))
			)
WHERE		iuse_new.[bti_databaseid] IS NULL
			AND iuse.[bti_enddate] IS NULL


--- Add new records
INSERT INTO [dbo].[tblMonitor_DbTblInd_Names]
(
	[bti_startdate],
	[bti_databaseid],
	[bti_tableid],
	[bti_indexid],
	[bti_databasename],
	[bti_tablename],
	[bti_indexname]
)
SELECT		iuse_new.[bti_startdate],
			iuse_new.[bti_databaseid],
			iuse_new.[bti_tableid],
			iuse_new.[bti_indexid],
			iuse_new.[bti_databasename],
			iuse_new.[bti_tablename],
			iuse_new.[bti_indexname]
FROM		[dbo].[tblMonitor_DbTblInd_Names] iuse
RIGHT JOIN	@sourceNames iuse_new ON iuse.[bti_databaseid] = iuse_new.[bti_databaseid]
			AND iuse.[bti_tableid] = iuse_new.[bti_tableid]
			AND ISNULL(iuse.[bti_indexid], 0) = ISNULL(iuse_new.[bti_indexid], 0)
			AND iuse.[bti_databasename] = iuse_new.[bti_databasename]
			AND iuse.[bti_tablename] = iuse_new.[bti_tablename]
			AND ISNULL(iuse.[bti_indexname], SPACE(0)) = ISNULL(iuse_new.[bti_indexname], SPACE(0))
WHERE		iuse.[bti_databaseid] IS NULL
---
--- END:  Database, Table, Index names
---------------------------------------------------------------------
